﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using DTO;
namespace DAO
{
    public class HoiVienDAO
    {
         DataTable dt;
        List<string> dsThamSo;
        ArrayList dsGiaTri;
       
        
        public HoiVienDAO()
        {
            dt = new DataTable();
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            
        }
        public DataTable layThongTinHoiVien(string maHoiVien, string maVe)
        {
            dsThamSo.Add("@maHoiVien");
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maHoiVien);
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_layThongTinHoiVien", dsThamSo, dsGiaTri);
            return dt;
        }
        public DataTable layThongTinHoiVien(string key)
        {
            dsThamSo.Add("@key");
            dsGiaTri.Add(key);
            dt = DataProvider.ExecuteStoreProc("sp_layThongTinHoiVienFull", dsThamSo,dsGiaTri);
            return dt;
        }
        public void capNhatSoDam(float heso, float sodam, string maHoiVien,string maVe)
        {
            string sql = "update HoiVien set soDiemThuong +=  " + sodam * heso + " where ID_hoiVien = '" + maHoiVien + "'";
            DataProvider.ExecuteNoneQuery(sql);
            sql = "update VeMayBay set capNhatDam='True' where ID_veBay = '" + maVe + "'";
            DataProvider.ExecuteNoneQuery(sql);
        }
        public DataTable laySoDam_HeSo(string maVe)
        {
         
            dsThamSo.Add("@maVe");
           
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_timHeSoCongDam", dsThamSo, dsGiaTri);
            return dt;
        }
        public DataTable kiemTraVe(string maVe)
        {
            dsThamSo.Add("@maVe");

            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_kiemTraVe", dsThamSo, dsGiaTri);
            return dt;
        }
        public bool kiemTraHoiVien(string maHoiVien)
        {
            dsThamSo.Add("@maHoiVien");

            dsGiaTri.Add(maHoiVien);
            dt = DataProvider.ExecuteStoreProc("sp_kiemHoiVien", dsThamSo, dsGiaTri);
            if (dt.Rows.Count == 1)
                return true;
            else
                return false;
        }
        public int xoaHoiVien(string maHoiVien)
        {
            int n = 0;
            string sql = "update HoiVien set tinhTrang = 1 where ID_hoiVien = '" + maHoiVien + "'";
            n = DataProvider.ExecuteNoneQuery(sql);
            return n;
        }
        public int capNhapHoiVien(HoiVienDTO dto)
        {
           string ngaySinh = string.Format("{0:yyyy-MM-dd}", dto.NgaySinh);
            int n = 0;
            string sql = "update HoiVien set loaiHoiVien = "+dto.LoaiHoiVien+",hoTen = N'"+dto.HoTen+"',";
            sql += " ngaySinh = '" + ngaySinh + "',gioiTinh = '" + dto.GioTinh + "',email = '" + dto.Email + "',";
            sql += " soDiemThuong = "+dto.SoDiemThuong+",kichHoat = '"+dto.KichHoat+"'";
            sql += " where ID_hoiVien = '" + dto.ID_hoiVien1+ "'";
            n = DataProvider.ExecuteNoneQuery(sql);
            return n;
        }
        public bool kiemTraTenDangNhap(string tenDangNhap)
        {
            dsThamSo.Add("@tenDangNhap");

            dsGiaTri.Add(tenDangNhap);
            dt = DataProvider.ExecuteStoreProc("sp_kiemTraTenDangNhap", dsThamSo, dsGiaTri);
            if (dt.Rows.Count == 1)
                return true;
            else
                return false;
        }
        public int themHoiVien(HoiVienDTO dto)
        {   
            string ngaySinh = string.Format("{0:yyyy-MM-dd}", dto.NgaySinh);
            int n = 0;
            string sql = "insert into HoiVien(ID_hoiVien,loaiHoiVien,hoTen,ngaySinh,gioiTinh,email,taiKhoanDangNhap,soDiemThuong,kichHoat,tinhTrang)";
            sql += " values('" + dto.ID_hoiVien1 + "'," + dto.LoaiHoiVien + ",N'" + dto.HoTen + "','" + ngaySinh + "','" + dto.GioTinh + "','" + dto.Email + "','" + dto.TenDangNhap + "',0,'" + dto.KichHoat + "',0)";
            n = DataProvider.ExecuteNoneQuery(sql);
            return n;
        }
        public int demSoLuongHoiVien()
        {

            dt = DataProvider.ExecuteStoreProc("sp_demSoLuongHoiVien", null,null);
            return dt.Rows.Count;
        }
    }
}
